import akshare as ak
from connector import mysqlConnectorHelper as mc
import datetime
import SimpleMySqlClass as smc

# 获取数据库链接
def get_mysql_conn():
    conn = mc.MySQLConnector("127.0.0.1", "haojg", "haojg", "stocks")
    return conn


def get_data_and_read(code):
    # 拉取远端数据
    stock_qfq_data = get_stock_data(code)
    # 获取链接
    conn = get_mysql_conn()
    # 落库
    save_data_to_mysql(conn, code, stock_qfq_data)
    # 读数
    read_data_from_mysql(conn)
    # 关闭连接
    conn.close()


# 1, get akshare stock data
def get_stock_data(code):
    stock_qfq_data = ak.stock_zh_a_hist(
        symbol=code, adjust="qfq", start_date="20230101", end_date="20231230"
    )
    stock_qfq_data = stock_qfq_data.iloc[:, 0:7]

    # 日期     开盘     收盘     最高     最低     成交量  成交额
    # 列名记得这样定义好
    stock_qfq_data.columns = [
        "date",
        "open",
        "close",
        "high",
        "low",
        "volume",
        "amount",
    ]
    print(stock_qfq_data)
    return stock_qfq_data


# 2, save data to mysql
def save_data_to_mysql(conn, code, stock_data_df):

    for index, row in stock_data_df.iterrows():
        # 插入新记录
        data = {
            "id": 0,
            "sd_date": row["date"],
            "sd_code": code,
            "sd_open": row["open"],
            "sd_high": row["high"],
            "sd_low": row["low"],
            "sd_close": row["close"],
            "sd_volume": row["volume"],
            "sd_amount": row["amount"],
            "create_time": datetime.datetime.now(),
        }
        rs = conn.insert("t_stock_k_data", data)
        print("rs = ", rs)
    print("-------------------------------")
    return


# 3, query data from mysql
def read_data_from_mysql(conn):
    tableName = "t_stock_k_data"
    # sql = f"select sd_date, sd_code, sd_open, sd_high, sd_low, sd_close, sd_volume, sd_amount from {tableName}"
    sql = f"select count(*) from {tableName}"
    rs = conn.query(sql)
    print("query rs = ", rs)
    pass


if __name__ == "__main__":
    code = "002202"
    get_data_and_read(code)
    print("main")
